package com.yehongyu.mytools.poidoc;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.extractor.ExcelExtractor;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.extractor.XSSFExcelExtractor;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {
	
	private static final Logger logger = Logger.getLogger(ExcelUtil.class);
	
	
	/**
	 * 从excel 2003文档中提取纯文本
	 * @param is
	 * @return
	 * @throws IOException
	 */
	public static String extractTextFromXLS2003(String fileName){
		StringBuffer content = new StringBuffer();
		
		// 创建对Excel工作簿文件的引用
		HSSFWorkbook wb = null;
		try {
			wb = new HSSFWorkbook(new FileInputStream(fileName));
		}  catch (IOException e) {
			logger.error("not HSSF(97-2003) format Excel!",e);
			return null;
		}
		// 循环工作表Sheet
		for (int numSheets = 0; numSheets < wb.getNumberOfSheets(); numSheets++) {
			HSSFSheet aSheet = wb.getSheetAt(numSheets);
			if(aSheet == null)
				continue;
			// 循环行Row
			for (int rowNumOfSheet = 0; rowNumOfSheet <= aSheet.getLastRowNum(); rowNumOfSheet++) {
				HSSFRow aRow = aSheet.getRow(rowNumOfSheet);
				if(aRow == null)
					continue;
				// 循环列Cell
				for (int cellNumOfRow = 0; cellNumOfRow <= aRow.getLastCellNum(); cellNumOfRow++) {
					HSSFCell aCell = aRow.getCell(cellNumOfRow);
					if(aCell == null)
						continue;
					if (aCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
						content.append(aCell.getNumericCellValue());
					} else if (aCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
						content.append(aCell.getBooleanCellValue());
					} else {
						content.append("Unknown cellType is:" + aCell.getCellType());
					}
				}
			}
		}
		return content.toString();
	}
	
	/**
	 * 从excel 2007文档中提取纯文本
	 * @param fileName
	 * @return
	 * @throws Exception
	 */
	public static String extractTextFromXLSX2007(String fileName) {
		StringBuffer content = new StringBuffer();
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
		XSSFWorkbook xwb = null;
		try {
			xwb = new XSSFWorkbook(fileName);
		} catch (IOException e) {
			logger.error("not XSSF(2007) format Excel!",e);
			return null;
		}
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < xwb.getNumberOfSheets(); numSheet++) {
			XSSFSheet xSheet = xwb.getSheetAt(numSheet);
			if (xSheet == null) {
				continue;
			}
			// 循环行Row
			for (int rowNum = 0; rowNum <= xSheet.getLastRowNum(); rowNum++) {
				XSSFRow xRow = xSheet.getRow(rowNum);
				if (xRow == null) {
					continue;
				}
				// 循环列Cell
				for (int cellNum = 0; cellNum <= xRow.getLastCellNum(); cellNum++) {
					XSSFCell xCell = xRow.getCell(cellNum);
					if (xCell == null) {
						continue;
					}
					if (xCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
						content.append(xCell.getBooleanCellValue());
					} else if (xCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
						content.append(xCell.getNumericCellValue());
					} else {
						content.append("Unknown cellType is:" + xCell.getCellType());
					}
				}
			}
		}
		return content.toString();
	}    
	
	/**
	 * 读取Excel文档
	 * @param filename
	 * @return 文本
	 */
	public static String readExcel(String filename) {
		Workbook wb = null;
		org.apache.poi.ss.extractor.ExcelExtractor extractor = null;
		try{	//解析Excel文档
			wb = new HSSFWorkbook(new FileInputStream(filename));
			extractor = new ExcelExtractor((HSSFWorkbook)wb);
		}catch(Exception ee){
			logger.warn("not HSSF(97-2003) format Excel! try XSSF(2007) format Excel!");
			try{
				wb = new XSSFWorkbook(new FileInputStream(filename));	
				extractor = new XSSFExcelExtractor((XSSFWorkbook)wb);
			}catch(Exception e){
				logger.error("not HSSF(97-2003) format Excel!error is:",ee);
				logger.error("not HSSF(97-2003) also not XSSF(2007) format Excel!",e);
				return null;
			}
		}
		extractor.setFormulasNotResults(true);
		extractor.setIncludeSheetNames(false);
		String content = extractor.getText();
		return content;
	}

	/**
	 * 输出Excel文档，测试
	 * @param filename
	 */
	public static void printExcel(String filename) {
		Workbook wb = null;
		try{	//解析Excel文档
			wb = new HSSFWorkbook(new FileInputStream(filename));
		}catch(Exception ee){
			logger.warn("not HSSF(97-2003) format Excel! try XSSF(2007) format Excel!");
			try{
				wb = new XSSFWorkbook(new FileInputStream(filename));			
			}catch(Exception e){
				logger.error("not HSSF(97-2003) format Excel!error is:",ee);
				logger.error("not HSSF(97-2003) also not XSSF(2007) format Excel!",e);
				return;
			}
		}
		//处理Excel文档
		int sheets = wb.getNumberOfSheets();
		System.out.println(filename + " has [" + sheets + "] Sheets!");
		for (int n = 0; n < sheets; n++) {
			Sheet sheet = wb.getSheetAt(n);
			if (sheet == null || n>10 )
				continue;
			int rows = sheet.getLastRowNum() + 1;
			System.out.println("=======================Sheet[" + n + "][" + sheet.getPhysicalNumberOfRows() + "/" + rows + "rows]——" + sheet.getSheetName());
			for (int i = 0; i < rows; i++) {
				Row row = sheet.getRow(i);
				if (row == null){
					System.out.println("Row-" + i + "[null]");
					continue;
				}
				int cells = row.getLastCellNum() + 1;
				System.out.print("Row[" + row.getRowNum() + "][" + row.getPhysicalNumberOfCells() + "/" + cells + "cells][");
				for (int m = 0; m < cells; m++) {
					Cell cell = row.getCell(m);
					String value;
					if (cell != null){
						System.out.print("Cell-" + cell.getColumnIndex() + "[");
						if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
							//先看是否是日期格式  
	                        if(DateUtil.isCellDateFormatted(cell)){  
	                            //读取日期格式  
	                        	value = String.valueOf(cell.getDateCellValue());  
	                        }else{  
	                            //读取数字  
	                        	value = String.valueOf(cell.getNumericCellValue());  
	                        }
						}else if(cell.getCellType()==Cell.CELL_TYPE_BOOLEAN){
							value = String.valueOf(cell.getBooleanCellValue());
						}else if(cell.getCellType()==Cell.CELL_TYPE_ERROR){
							value = String.valueOf(cell.getErrorCellValue());
						}else if(cell.getCellType()==Cell.CELL_TYPE_STRING){
							value = cell.getRichStringCellValue().toString();
						}else if(cell.getCellType()==Cell.CELL_TYPE_BLANK){
							value = cell.getStringCellValue();
						}else if(cell.getCellType()==Cell.CELL_TYPE_FORMULA){
							value = cell.getCellFormula();
						}else{
							//取值类型不对，忽略
							value = "Unknown cellType is:" + cell.getCellType();
						}
						System.out.print(value + "]");
					}else{
						System.out.print("Cell-" + m + "[null]");
					}
				}
				System.out.print("]\r\n");
			}
		}
	}
	
	/**
	 * 创建Excel2003文档,测试
	 */
	public static void createExcel2003(){
		Workbook wb = new HSSFWorkbook();// 建立新HSSFWorkbook对象
		//获得CreationHelper对象,这个应该是一个帮助类  
        CreationHelper helper = wb.getCreationHelper();  
		
		Sheet sheet = wb.createSheet("new sheet");// 建立新的sheet对象
        //设置每个sheet每一行的宽度,自动,根据需求自行确定  
        sheet.autoSizeColumn(1, true);  

		// Create a row and put some cells in it. Rows are 0 based.
		Row row = sheet.createRow(0);// 建立新行
		//设置行长度自动             
        //row.setHeight((short)500);  
        row.setHeightInPoints(20);  
        //row.setZeroHeight(true); 

		/**row.createCell(0)------------------**/
		Cell cell = row.createCell(0);// 建立新cell
		cell.setCellValue(1);// 设置cell的整数类型的值
		/**row.createCell(1)------------------**/
		row.createCell(1).setCellValue(1.2);// 设置cell浮点类型的值
		/**row.createCell(2)------------------**/
		row.createCell(2).setCellValue("test");// 设置cell字符类型的值
		/**row.createCell(3)------------------**/
		row.createCell(3).setCellValue(true);// 设置cell布尔类型的值
		/**row.createCell(4)------------------**/
		CellStyle cellStyle = wb.createCellStyle();// 建立新的cell样式
		// 设置cell样式为定制的日期格式
		Cell dCell = row.createCell(4);
		dCell.setCellValue(new Date());// 设置cell为日期类型的值
		dCell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
		/**row.createCell(5)------------------**/
		Cell csCell = row.createCell(5);
		// csCell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell编码解决中文高位字节截断
		csCell.setCellValue("中文测试_Chinese Words Test");// 设置中西文结合字符串
		/**row.createCell(6)------------------**/
		row.createCell(6).setCellType(Cell.CELL_TYPE_ERROR);// 建立错误cell
		/**row.createCell(7)------------------**/
		cell = row.createCell(7);
		//创建一个基本的样式  
        cellStyle = ExcelCellUtil.createStyleCell(wb);  
        //设置文字在单元格里面的位置  
        cellStyle = ExcelCellUtil.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);  
        //先创建字体样式,并把这个样式加到单元格的字体里面  
        cellStyle.setFont(ExcelCellUtil.createFonts(wb));  
        //把这个样式加到单元格里面  
        cell.setCellStyle(cellStyle);                     
        //给单元格设值  
        cell.setCellValue(true); 
		/**row.createCell(8)------------------**/ 
		cell = row.createCell(8);
		//设置文字在单元格里面的位置  
        cellStyle = ExcelCellUtil.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);  
        //设置这个样式的格式(Format)  
        cellStyle = ExcelCellUtil.setCellFormat(helper,cellStyle, "#,##0.0000");                    
        //先创建字体样式,并把这个样式加到单元格的字体里面  
        cellStyle.setFont(ExcelCellUtil.createFonts(wb));  
        //把这个样式加到单元格里面  
        cell.setCellStyle(cellStyle);  
        //给单元格设值  
        cell.setCellValue(new Double(2008.2008)); 
		/**row.createCell(9)------------------**/ 
		cell = row.createCell(9);
        cellStyle = ExcelCellUtil.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);                      
        cellStyle.setFont(ExcelCellUtil.createFonts(wb));  
        cell.setCellStyle(cellStyle);  
        cell.setCellValue(helper.createRichTextString("RichString"));  
		/**row.createCell(10)------------------**/ 
		cell = row.createCell(10);
        cellStyle = ExcelCellUtil.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);  
        cellStyle = ExcelCellUtil.setCellFormat(helper,cellStyle, "MM-yyyy-dd");  
        cell.setCellStyle(cellStyle);  
        cell.setCellValue(new Date());  
		/**row.createCell(11)------------------**/ 
		cell = row.createCell(11);
        cellStyle = ExcelCellUtil.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);  
        cellStyle.setFont(ExcelCellUtil.createFonts(wb));  
        //设置公式  
        cell.setCellFormula("SUM(E"+(1)+":X"+(1)+")");  
		/**row.createCell(12)------------------**/ 
		cell = row.createCell(12);
        cellStyle = ExcelCellUtil.setCellStyleAlignment(cellStyle, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER);  
        cellStyle = ExcelCellUtil.setFillBackgroundColors(cellStyle,IndexedColors.ORANGE.getIndex(),IndexedColors.ORANGE.getIndex(),CellStyle.SOLID_FOREGROUND);  
        cell.setCellStyle(cellStyle);  
        cell.setCellValue(1);  
        
		// Write the output to a file
		String filename = ClassLoader.getSystemResource("").getPath() + "workbook.xls";
		logger.info(filename);
		try {
			FileOutputStream fileOut = new FileOutputStream(filename);
			wb.write(fileOut);
			fileOut.close();
		} catch (IOException e) {
			logger.error("exception io error!",e);
		}
	}
	
	/**
	 * 创建Excel2007文档,测试
	 */
	public static void createExcel2007(){
		XSSFWorkbook wb = new XSSFWorkbook();// 建立新HSSFWorkbook对象
		XSSFSheet sheet = wb.createSheet("new sheet");// 建立新的sheet对象

		// Create a row and put some cells in it. Rows are 0 based.
		XSSFRow row = sheet.createRow(0);// 建立新行

		// Create a cell and put a value in it.
		XSSFCell cell = row.createCell(0);// 建立新cell
		cell.setCellValue(1);// 设置cell的整数类型的值

		// Or do it on one line.
		row.createCell(1).setCellValue(1.2);// 设置cell浮点类型的值
		row.createCell(2).setCellValue("test");// 设置cell字符类型的值
		row.createCell(3).setCellValue(true);// 设置cell布尔类型的值
		XSSFCellStyle cellStyle = wb.createCellStyle();// 建立新的cell样式
		// 设置cell样式为定制的日期格式
		XSSFCell dCell = row.createCell(4);
		dCell.setCellValue(new Date());// 设置cell为日期类型的值
		dCell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
		XSSFCell csCell = row.createCell(5);
		// csCell.setEncoding(HSSFCell.ENCODING_UTF_16);// 设置cell编码解决中文高位字节截断
		csCell.setCellValue("中文测试_Chinese Words Test");// 设置中西文结合字符串
		row.createCell(6).setCellType(XSSFCell.CELL_TYPE_ERROR);// 建立错误cell

		// Write the output to a file
		String filename = ClassLoader.getSystemResource("").getPath() + "workbook.xlsx";
		logger.info(filename);
		try {
			FileOutputStream fileOut = new FileOutputStream(filename);
			wb.write(fileOut);
			fileOut.close();
		} catch (IOException e) {
			logger.error("exception io error!",e);
		}
	}
	
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
//		ExcelUtil.createExcel2003();
//		ExcelUtil.createExcel2007();
//		ExcelUtil.printExcel("\\\\YingYang\\software\\2008-WeeklyReport-英扬.xls");
//		ExcelUtil.printExcel("\\\\YingYang\\software\\2008-weeklyreport-yingyang.xlsx");
//		System.out.println(ExcelUtil.readExcel("D:\\dev\\otherwork\\codeutil\\target\\classes\\workbook.xls"));
//		System.out.println(ExcelUtil.readExcel("D:\\dev\\otherwork\\codeutil\\target\\classes\\workbook.xlsx"));
//		System.out.println(ExcelUtil.extractTextFromXLS2003("D:\\dev\\otherwork\\codeutil\\target\\classes\\workbook.xls"));
//		System.out.println(ExcelUtil.extractTextFromXLSX2007("D:\\dev\\otherwork\\codeutil\\target\\classes\\workbook.xlsx"));
	}

}
